--    Author    : ChenErHao
--    Name      : DM.T_MTH_PANDECT_DEP_BALANCE.HQL
--    Functions : 总览-存款
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_PANDECT_DEP_BALANCE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 

SELECT
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.PRODUCT_CATEGORY,
    T1.CUSTOMER_TYPE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.DEP_BALANCE end),0) DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then T1.DEP_BALANCE end),0)  LAST_MTH_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_12M_END_DATE#' then T1.DEP_BALANCE end),0)  LAST_YEAR_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0)   WEIGHT_AMT,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then T1.WEIGHT_AMT end),0)   LAST_MTH_WEIGHT_AMT,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_12M_END_DATE#' then T1.WEIGHT_AMT end),0)  LAST_YEAR_WEIGHT_AMT
FROM
    (
        SELECT  --第二层机构统计
            DATA_DATE AS FISCAL_MTH_DIMNSN_ID,
            BELONG_AREA_CODE AS AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END     ORG_ID,
            CASE WHEN C.depb_category_id_1 IS NULL THEN '9999'
                 WHEN C.depb_category_id_1='' THEN '9999'
                 ELSE C.depb_category_id_1 END    PRODUCT_CATEGORY,
            CUSTOMER_TYPE         CUSTOMER_TYPE,
            SUM(DEP_BALANCE)      DEP_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
          (SELECT DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY, CUSTOMER_TYPE,DEP_BALANCE,LOAN_OCCURRENCE_LEVEL 
          FROM EDW.DEPB 
        WHERE
            DATA_DATE IN( '#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#'))A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B
        ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else
            A.DATA_ORG_CODE end= B.ORG_ID
        LEFT JOIN
            DMCODE.T_DEPB_CATEGORY C
        ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else
            A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID
        GROUP BY
            DATA_DATE ,
            A.BELONG_AREA_CODE,
            B.ORG_LVL_ID_2,
            C.depb_category_id_1,
            CUSTOMER_TYPE
        union all
        SELECT  --第三层机构统计
            DATA_DATE              FISCAL_MTH_DIMNSN_ID,
            BELONG_AREA_CODE        AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_3 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_3='' THEN '9999'
                 ELSE B.ORG_LVL_ID_3 END     ORG_ID,
            CASE WHEN C.depb_category_id_1 IS NULL THEN '9999'
                 WHEN C.depb_category_id_1='' THEN '9999'
                 ELSE C.depb_category_id_1 END    PRODUCT_CATEGORY,
            CUSTOMER_TYPE         CUSTOMER_TYPE,
            SUM(DEP_BALANCE)      DEP_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
             (SELECT DATA_DATE,BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY, CUSTOMER_TYPE,DEP_BALANCE,LOAN_OCCURRENCE_LEVEL 
             FROM EDW.DEPB 
        WHERE
            DATA_DATE IN( '#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#'))A
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL  B
        ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else
            A.DATA_ORG_CODE end= B.ORG_ID
        LEFT JOIN
            DMCODE.T_DEPB_CATEGORY C
        ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else
            A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID
        GROUP BY
            DATA_DATE ,
            A.BELONG_AREA_CODE,
            B.ORG_LVL_ID_3,
            C.depb_category_id_1,
            CUSTOMER_TYPE    
            ) T1 
      GROUP BY 
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.PRODUCT_CATEGORY,
    T1.CUSTOMER_TYPE  
;